﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GFMS.MODEL;
using KPIS.GERP.GFMS.IDAL;

namespace KPIS.GERP.GFMS.DAL
{
    public class D_HRM_MP_Jobsites : IDAL_HRM_MP_Jobsites
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();
        

        public D_HRM_MP_Jobsites()
        {

        }

        public IList<M_HRM_MP_Jobsites> GetGFMISList(string filter)
        {
            return new List<M_HRM_MP_Jobsites>();
            string strSQL;
            DataRowCollection drc = null;
            IList<M_HRM_MP_Jobsites> infoList = new List<M_HRM_MP_Jobsites>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@filter", "%" + filter + "%");

                strSQL = "SELECT * "
                         // + " FROM (SELECT j.job_site_gfmis"
                         //j.job_site_seq, concat(j.job_site_gfmis, ' - ', j.job_site_name) as job_site_name"

                         ///+ " from hrm_mp_job_sites j"
                         //+ " WHERE j.record_status not like @delete_status"
                         //+ " AND coalesce(j.job_site_gfmis, '') = ''"
                        ///+ ") t"
                    + (filter.Length > 0 ? " WHERE job_site_gfmis like @filter" : "")
                    + " ORDER BY job_site_gfmis";
                    //+ (filter.Length > 0 ? " WHERE job_site_name like @filter" : "")
                    //+ " ORDER BY job_site_name";
                

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_HRM_MP_Jobsites info = new M_HRM_MP_Jobsites();
                        info.job_site_seq = Convert.ToInt32(drc[i]["job_site_seq"]);
                        info.job_site_name = drc[i]["job_site_name"].ToString();
                        //info.job_site_gfmis = drc[i]["job_site_gfmis"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public M_HRM_MP_Jobsites GetGFMISInfo(int seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_HRM_MP_Jobsites info = new M_HRM_MP_Jobsites();

            bool bOK = false;
            int oldSeq = seq;
            int newSeq = -1;
            try
            {
                while (!bOK)
                {
                    newSeq = GetGFMISSeq(oldSeq);
                    
                    if(oldSeq == newSeq)
                    {
                        bOK = true;
                    }
                    else
                    {
                        oldSeq = newSeq;
                    }
                }

                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", newSeq);

                strSQL = "SELECT j.job_site_seq, j.job_site_ref, j.job_site_name, j.job_site_name_short"
                    + ", j.job_site_upper_seq, j.job_site_type_seq, j.work_area_seq, j.job_site_gfmis"
                    + ", j.job_site_level_seq"
                    + " FROM hrm_mp_job_sites j"
                    //+ " LEFT JOIN @.hrm_mp_job_site_types jt ON j.job_site_type_seq = jt.job_site_type_seq"
                    //+ " LEFT JOIN @.hrm_mp_work_areas wa ON j.work_area_seq = wa.work_area_seq"
                    //+ " LEFT JOIN @.hrm_mp_job_site_levels l ON j.job_site_level_seq = l.job_site_level_seq"
                    + " where j.job_site_seq = @seq";
                

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.job_site_seq = Convert.ToInt32(drc[0]["job_site_seq"]);
                    info.job_site_ref = drc[0]["job_site_ref"].ToString();
                    info.job_site_name = drc[0]["job_site_name"].ToString();
                    info.job_site_gfmis = drc[0]["job_site_gfmis"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        private int GetGFMISSeq(int seq)
        {
            string strSQL;
            int nResultSeq = -1;
            DataRowCollection drc = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT case when COALESCE(j.job_site_gfmis, '') = '' then j.job_site_upper_seq"
                        + " else j.job_site_seq"
                        + " end as job_site_seq"
                    + " FROM hrm_mp_job_sites j"
                    + " WHERE j.job_site_seq = @seq";
                
                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;

                if (drc.Count > 0)
                {
                    nResultSeq = Convert.ToInt32(drc[0]["job_site_seq"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return nResultSeq;
        }
    }
}
